﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using Ivy.Common.Framework.Base;
using Ivy.Common.Framework.DataAccess;
using Ivy.UserManagement.DataEntity;
using Ivy.UserManagement.DomainEntity;

namespace Ivy.UserManagement.DataAccess
{
    public class UserDao : User, IDaoInterface<User, UserCriteria>
    {
        #region Variables & Constants
        #endregion Variables & Constants

        #region Constructor & Destructor
        public UserDao() : base() { }
        public UserDao(User User)
        {
            this.UserKey = User.UserKey;
            this.UserLoginID = User.UserLoginID;
            this.UserPassword = User.UserPassword;
            this.UserFirstName = User.UserFirstName;
            this.UserLastName = User.UserLastName;
            this.UserPrimaryEmailID = User.UserPrimaryEmailID;
            this.UserVerificationCode = User.UserVerificationCode;
            this.UserIsVarified = User.UserIsVarified;

            this.Sys_CreatedByID = User.Sys_CreatedByID;
            this.Sys_ModifiedByID = User.Sys_ModifiedByID;
            this.Sys_DateOfCreation = User.Sys_DateOfCreation;
            this.Sys_DateOfModification = User.Sys_DateOfModification;
            this.Sys_DeleteType = User.Sys_DeleteType; 
            this.Sys_IsDeleted = User.Sys_IsDeleted;
            this.Sys_OrganizationID = User.Sys_OrganizationID;
            this.Sys_InitialPrimaryKeys = User.Sys_InitialPrimaryKeys;
            this.Sys_WorkstationName = User.Sys_WorkstationName;
            this.ConcurrencyTimeStamp = User.ConcurrencyTimeStamp;
            this.EntityState = User.EntityState;
        }
        #endregion Constructor & Destructor

        #region Public Properties
        #endregion Public Properties

        #region Public Methods
        #region IDaoInterface<User,UserCriteria> Members
        /// <summary>
        /// Created by Mrinmoy Das
        /// </summary>
        /// <returns></returns>
        public bool Insert()
        {
            bool IsOperationSuccessful = false;
            try
            {
                DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
                CmdObj.CommandText = "sp_User_Insert";
                //CmdObj.CommandText = UserManagementSQLCommandText.InsertUser();
                CmdObj.CommandType = CommandType.StoredProcedure;
                //CmdObj.CommandType = CommandType.Text;

                DataAccessProviderUtility.AddParameter(CmdObj, "@UserLoginID", ParameterDirection.Input, DbType.String, this.UserLoginID);
                DataAccessProviderUtility.AddParameter(CmdObj, "@UserPassword", ParameterDirection.Input, DbType.String, this.UserPassword);
                DataAccessProviderUtility.AddParameter(CmdObj, "@UserFirstName", ParameterDirection.Input, DbType.String, this.UserFirstName);
                DataAccessProviderUtility.AddParameter(CmdObj, "@UserLastName", ParameterDirection.Input, DbType.String, this.UserLastName);
                DataAccessProviderUtility.AddParameter(CmdObj, "@UserPrimaryEmailID", ParameterDirection.Input, DbType.String, this.UserPrimaryEmailID);
                DataAccessProviderUtility.AddParameter(CmdObj, "@UserVerificationCode", ParameterDirection.Input, DbType.String, this.UserVerificationCode);
                DataAccessProviderUtility.AddParameter(CmdObj, "@UserIsVarified", ParameterDirection.Input, DbType.Boolean , this.UserIsVarified);
                
                DatabaseCommon.InsertStoredProcForParameterDatEntityBase(CmdObj, this);
                
                DataAccessProviderUtility.AddParameter(CmdObj, "@UserKey", ParameterDirection.Output, DbType.Int32, DBNull.Value);
                DataAccessProviderUtility.AddParameter(CmdObj, "@Sys_DateOfCreation", ParameterDirection.Output, DbType.DateTime, DBNull.Value);

                CmdObj.Connection = DbConn;
                DbConn.Open();
                CmdObj.Prepare();
                int lNoOfRecords = CmdObj.ExecuteNonQuery();
                if (lNoOfRecords == 1)
                {
                    this.UserKey = Convert.ToInt32(CmdObj.Parameters["@UserKey"].Value);
                    this.Sys_DateOfCreation = Convert.ToDateTime(CmdObj.Parameters["@Sys_DateOfCreation"].Value);
                    this.Sys_DateOfModification = this.Sys_DateOfCreation;
                    this.EntityState = EntityState.UnChanged;
                    IsOperationSuccessful = true;
                }
                CmdObj.Dispose();
                DbConn.Close();
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }
            return IsOperationSuccessful;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public bool Update()
        {
            bool IsOperationSuccessful = false;
             try
            {
                DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
                CmdObj.CommandText = "sp_User_Update";
                CmdObj.CommandType = CommandType.StoredProcedure;

                DataAccessProviderUtility.AddParameter(CmdObj, "UserKey", ParameterDirection.Input, DbType.Int32, this.UserKey);
                DataAccessProviderUtility.AddParameter(CmdObj, "UserLoginID", ParameterDirection.Input, DbType.String, this.UserLoginID);
                DataAccessProviderUtility.AddParameter(CmdObj, "UserPassword", ParameterDirection.Input, DbType.String, this.UserPassword);
                DataAccessProviderUtility.AddParameter(CmdObj, "UserFirstName", ParameterDirection.Input, DbType.String, this.UserFirstName);
                DataAccessProviderUtility.AddParameter(CmdObj, "UserLastName", ParameterDirection.Input, DbType.String, this.UserLastName);
                DataAccessProviderUtility.AddParameter(CmdObj, "UserPrimaryEmailID", ParameterDirection.Input, DbType.String, this.UserPrimaryEmailID);
                DataAccessProviderUtility.AddParameter(CmdObj, "UserVerificationCode", ParameterDirection.Input, DbType.String, this.UserVerificationCode);
                DataAccessProviderUtility.AddParameter(CmdObj, "UserIsVarified", ParameterDirection.Input, DbType.Boolean, this.UserIsVarified);

                DatabaseCommon.UpdateStoredProcForParameterDatEntityBase(CmdObj, this);
                
                DataAccessProviderUtility.AddParameter(CmdObj, "Sys_DateOfModification" ,ParameterDirection.Output,DbType.DateTime,DBNull.Value);

                CmdObj.Connection = DbConn;
                DbConn.Open();
                CmdObj.Prepare();
                int lNoOfRecords = CmdObj.ExecuteNonQuery();
                if (lNoOfRecords==1)
                {
                    this.Sys_DateOfModification = Convert.ToDateTime(CmdObj.Parameters["@Sys_DateOfModification"].Value);
                    this.EntityState = EntityState.UnChanged;
                    IsOperationSuccessful = true;
                }

                CmdObj.Dispose();
                DbConn.Close();

            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }
             return IsOperationSuccessful;
        }

        public bool Delete()
        {
            throw new NotImplementedException();
        }

        public User GetByID(string ID)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public List<User> GetAll()
        {
            List<User> lUserList = new List<User>();
            try
            {
                DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
                CmdObj.CommandText = "sp_User_GetAll";
                CmdObj.CommandType = CommandType.StoredProcedure;

                CmdObj.Connection = DbConn;
                DbConn.Open();
                CmdObj.Prepare();

                DbDataReader lDataReader = CmdObj.ExecuteReader();
                if (lDataReader.HasRows)
                {
                    while (lDataReader.Read())
                    {
                        User lTempUser = new User();
                        this.TranslateUserFieldsFromReaderToEntity(lTempUser, lDataReader);
                        lUserList.Add(lTempUser);
                    }
                }
                CmdObj.Dispose();
                DbConn.Close();
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }

            return lUserList;
        }

        public List<User> GetByCriteria(UserCriteria CriteriaEntity)
        {
            List<User> lUserList = new List<User>();
            try
            {
                DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
                CmdObj.CommandText = "sp_User_GetByCriteria";
                CmdObj.CommandType = CommandType.StoredProcedure;

                DataAccessProviderUtility.AddParameter(CmdObj, "@UserLoginID", ParameterDirection.Input, DbType.String, this.UserLoginID);
                DataAccessProviderUtility.AddParameter(CmdObj, "@UserPassword", ParameterDirection.Input, DbType.String, this.UserPassword);
                DataAccessProviderUtility.AddParameter(CmdObj, "@UserFirstName", ParameterDirection.Input, DbType.String, this.UserFirstName);
                DataAccessProviderUtility.AddParameter(CmdObj, "@UserLastName", ParameterDirection.Input, DbType.String, this.UserLastName);
                DataAccessProviderUtility.AddParameter(CmdObj, "@UserPrimaryEmailID", ParameterDirection.Input, DbType.String, this.UserPrimaryEmailID);
                DataAccessProviderUtility.AddParameter(CmdObj, "@UserVerificationCode", ParameterDirection.Input, DbType.String, this.UserVerificationCode);
                DataAccessProviderUtility.AddParameter(CmdObj, "@UserIsVarified", ParameterDirection.Input, DbType.Boolean, this.UserIsVarified);

                DatabaseCommon.DefaultStoredProcParameterValuesForGetByCriteria(CmdObj, CriteriaEntity);

                CmdObj.Connection = DbConn;
                DbConn.Open();
                CmdObj.Prepare();

                DbDataReader lDataReader = CmdObj.ExecuteReader();
                if (lDataReader.HasRows)
                {
                    while (lDataReader.Read())
                    {
                        User lTempUser = new User();
                        this.TranslateUserFieldsFromReaderToEntity(lTempUser, lDataReader);
                        lUserList.Add(lTempUser);
                    }
                }
                CmdObj.Dispose();
                DbConn.Close();
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }

            return lUserList;
        }

        #endregion

        public User GetByLoginID(string LoginID)
        {
            return GetByLoginID(LoginID, false, -1);
        }

        public User GetByLoginID(string LoginID, bool IsDeleted, Int32 OrganizationID)
        {
            try
            {

                if (LoginID != string.Empty)
                {

                    DbConnection dbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                    DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
                    CmdObj.CommandText = "sp_User_GetByLoginID";
                    CmdObj.CommandType = CommandType.StoredProcedure;

                    DataAccessProviderUtility.AddParameter(CmdObj, "@UserLoginID", ParameterDirection.Input, DbType.String, LoginID);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@Sys_IsDeleted", ParameterDirection.Input, DbType.Boolean, IsDeleted);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@Sys_OrganizationID", ParameterDirection.Input, DbType.Int32, OrganizationID);

                    CmdObj.Connection = dbConn;
                    dbConn.Open();
                    CmdObj.Prepare();

                    DbDataReader lDataReader = CmdObj.ExecuteReader();

                    if (lDataReader.HasRows)
                    {
                        if (lDataReader.Read())
                        {
                            this.TranslateUserFieldsFromReaderToEntity(this, lDataReader);
                        }
                    }

                    CmdObj.Dispose();
                    dbConn.Close();
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }

            return this;
        }

        #endregion Public Methods

        #region Events
        #endregion Events

        #region Private Methods

        /// <summary>
        /// Created by Mrinmoy Das
        /// </summary>
        /// <param name="pUser"></param>
        /// <param name="pDbDataReader"></param>
        private void TranslateUserFieldsFromReaderToEntity(User pUser, DbDataReader pDbDataReader)
        {
            pUser.UserKey = Convert.ToInt32(pDbDataReader["UserKey"]);
            pUser.UserLoginID = Convert.ToString(pDbDataReader["UserLoginID"]);
            pUser.UserPassword = Convert.ToString(pDbDataReader["UserPassword"]);
            pUser.UserFirstName = Convert.ToString(pDbDataReader["UserFirstName"]);
            pUser.UserLastName = Convert.ToString(pDbDataReader["UserLastName"]);
            pUser.UserPrimaryEmailID = Convert.ToString(pDbDataReader["UserPrimaryEmailID"]);
            pUser.UserVerificationCode = Convert.ToString(pDbDataReader["UserVerificationCode"]);
            pUser.UserIsVarified = Convert.ToBoolean(pDbDataReader["UserIsVarified"]);

            DatabaseCommon.TranslateSystemFieldsFromReaderToEntity(pUser, pDbDataReader);
        }
        private void AdapterCommandBuild()
        {
            try
            {
                DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                DbCommand CmdObj = DbConn.CreateCommand(); //DataAccessProviderUtility.CreateCommand();
                DbDataAdapter lDataAdapter = DataAccessProviderUtility.CreateDataAdapter();
                DbCommandBuilder lCommandBuilder = DataAccessProviderUtility.CreateCommandBuilder();
                DataTable lDataTable = new DataTable("UserMaster");
                CmdObj.CommandText = "SELECT * FROM UserMaster WHERE UserKey = NULL";
                CmdObj.CommandTimeout = 10;
                lDataAdapter.SelectCommand = CmdObj;
                lDataAdapter.FillSchema(lDataTable, SchemaType.Source);
                lCommandBuilder.DataAdapter = lDataAdapter;
                lDataAdapter.InsertCommand = lCommandBuilder.GetInsertCommand();
                lDataAdapter.UpdateCommand = lCommandBuilder.GetUpdateCommand();
                lDataAdapter.DeleteCommand = lCommandBuilder.GetDeleteCommand();

                //using (DbCommand cmd = conn.CreateCommand())
                //    {
                //        cmd.CommandType = CommandType.Text;
                //        cmd.CommandText = "select * from " + tableName;
                //        cmd.CommandTimeout = 10;
                //        using (DbDataAdapter da = factory.CreateDataAdapter())
                //        {
                //            DbCommandBuilder cb = factory.CreateCommandBuilder();
                //            da.SelectCommand = cmd;
                //            DataTable dt = new DataTable();
                //            da.FillSchema(dt, SchemaType.Source);
                //            cb.DataAdapter = da;
                //            DbCommand[] cmds = new DbCommand[3]
                //            cmds[0] = cb.GetUpdateCommand();
                //            cmds[1] = cb.GetDeleteCommand();
                //            cmds[2] = cb.GetInsertCommand();
                //        }
                //    }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }
        }
        #endregion Private Methods


    }
}
